﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using COMExcel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.IO;
using System.Drawing;
using System.Data;
using RockCompany.Common.Constant;

namespace RockCompany.Common.Lib
{
    public class ExcelExporter
    {
        #region Properties
        public const string CONFIG_PATH = "\\Common\\ExcelTemplate\\";

        private string path;
        private COMExcel.Application excelApp;
        private COMExcel.Workbook templateExcelWB;
        private COMExcel.Worksheet curWS;

        private Translator _trans;
        public Translator Trans 
        {
            get { return _trans; }
            set { _trans = value; } 
        }
        #endregion

        #region Constructor
        public ExcelExporter()
        {
        }
        #endregion

        #region Main Methods
        public bool open(string sFileName)
        {
            try
            {
                path = System.IO.Path.GetFullPath(System.Windows.Forms.Application.StartupPath + CONFIG_PATH + sFileName + ".xls");
                //path = System.IO.Path.GetFullPath(System.Windows.Forms.Application.StartupPath + DBConnector.SOLUTION_DIR + CONFIG_PATH + sFileName + ".xls");
                excelApp = new COMExcel.Application();
                templateExcelWB = excelApp.Workbooks.Open(path, 0, false, 5, "", "", false, COMExcel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                curWS = (COMExcel.Worksheet)templateExcelWB.Worksheets[1];
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcel open : =================================" + ex.Message);
            }
            return curWS != null;
        }

        public void inputValue(string[] range, Object ob, bool isMerge)
        {
            try
            {
                COMExcel.Range r = (COMExcel.Range)curWS.get_Range(range[0], range[1]);
                r.Merge(isMerge);
                r.Value2 = ob;
                r.Font.Name = "Times New Roman";
                r.WrapText = true;
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcel input : =================================" + ex.Message);
            }
        }

        public void inputValue(string range, Object ob)
        {
            try
            {
                COMExcel.Range r = curWS.get_Range(range, range);
                r.Value2 = ob;
                r.Font.Name = "Times New Roman";
                r.WrapText = true;
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcel input : =================================" + ex.Message);
            }
        }

        public void inputRange(string[] range, Object ob, bool isMerge)
        {
            try
            {
                COMExcel.Range r = (COMExcel.Range)curWS.get_Range(range[0], range[1]);
                r.Merge(isMerge);
                r.Value2 = ob;
                r.Font.Name = "Times New Roman";
                r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.Black));
                r.WrapText = true;
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcel input : =================================" + ex.Message);
            }
        }

        public void inputRangeValues(DataTable db, List<DataColumn> displayableCols, int beginCol, int beginRow, bool isHasOrder)
        {
            if (isHasOrder)
            {
                DataColumn newCol = new DataColumn(CommonConstantIF.COL_ORDER);
                displayableCols.Insert(0, newCol);
            }
            for (int i = 0; i < displayableCols.Count; i++)
            {
                COMExcel.Range r = (COMExcel.Range)curWS.Cells[beginRow, beginCol + i];
                r.Value2 = Trans.getControlLabel("COL_" + displayableCols[i].ColumnName);
                r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.Black));
                r.Font.Bold = true;
                r.Font.Name = "Times New Roman";
                r.WrapText = true;
            }
            beginRow++;
            for (int i = 0; i < db.Rows.Count; i++)
            {
                COMExcel.Range r = (COMExcel.Range)curWS.Cells[beginRow + i, beginCol];
                r.Value2 = i + 1;
                r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.Black));
                ColorTranslator.ToOle(Color.Black);
                int j = 0;
                if (isHasOrder) { j = 1; }
                for (; j < displayableCols.Count; j++)
                {
                    r = (COMExcel.Range)curWS.Cells[beginRow + i, beginCol + j];
                    r.Value2 = db.Rows[i][displayableCols[j]];
                    r.Font.Name = "Times New Roman";
                    r.WrapText = true;
                    r.BorderAround(COMExcel.XlLineStyle.xlContinuous, COMExcel.XlBorderWeight.xlThin, COMExcel.XlColorIndex.xlColorIndexAutomatic, ColorTranslator.ToOle(Color.Black));
                    ColorTranslator.ToOle(Color.Black);
                }
            }
        }

        public bool saveExcel(string path)
        {
            try
            {
                templateExcelWB.SaveCopyAs(path);
            }
            catch (Exception ex)
            {
                Logger.addLog(Logger.ERROR + "\nExcel save : =================================" + ex.Message);
            }
            return File.Exists(path);
        }

        public void close()
        {
            // Release WorkSheet
            System.Runtime.InteropServices.Marshal.ReleaseComObject(curWS);
            templateExcelWB.Close(false, null, null);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(templateExcelWB);
            // Exit excel application
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
        }
        #endregion
    }
}
